package com.market.dao.jpa;

import java.text.SimpleDateFormat;
import java.util.List;

import javax.persistence.Query;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Root;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.market.dao.LocationDao;
import com.market.dao.PickupScheduleDao;
import com.market.domain.Location;
import com.market.domain.Order;
import com.market.domain.PickupSchedule;
import com.market.domain.User;

@Repository("locationDao")
public class LocationDaoJpa extends GenericDaoJpa<Location, Long> implements
		LocationDao {

	@Autowired
	private PickupScheduleDao calendarDao;

	public LocationDaoJpa() {
		super(Location.class);
	}

	public List<Location> findAllOrderByNumber() {
		String sql = "select location.* from location order by number asc";
		
		Query q = this.em.createNativeQuery(sql, Location.class);
		List<Location> locations = q.getResultList();
		return locations;
	}
	
	public void remove(Location object) {
		if (super.findById(object.getId()) == null) {
			log.debug(this.getClass() + " Location with id " + object.getId()
					+ " is not found, can not be removed");
		} else {
			log.debug(this.getClass() + " Location with id " + object.getId()
					+ " is found, will be removed");
			super.removeById(object.getId());
		}

	}

	public List<PickupSchedule> findPickupSchedules(Location location) {
		String sql = "select pickupschedule.* from pickupschedule,location_pickupschedule  where pickupschedule.id = "
				+ " location_pickupschedule.pickupschedules_id and location_pickupschedule.location_id = "
				+ location.getId() + "  order by pickupschedule.dayofweek asc";
		Query query = this.em.createNativeQuery(sql, PickupSchedule.class);
		List<PickupSchedule> pickupSchedules = query.getResultList();

		for (PickupSchedule schedule : pickupSchedules) {
			schedule.setSelected(true);
		}
		return pickupSchedules;
	}

	
	
	
	public List<User> findAllUsers(Location location) {
		String sql = "select user.* from user where dropOffLocation_id = " + location.getId() ;
		Query query = this.em.createNativeQuery(sql, User.class);
		List<User> users = query.getResultList();
		return users;
	}

	@Override
	public Location save(Location location) {
		// Delete pickupSchedules that are referenced by this location first,
		// because save location will add
		// add new pickupSchedules, but those pickupSchedules will have new id.
		// All this because location's pickupSchedules property have set Cascade
		// to all.
		// String sql =
		// "delete from pickupschedule where pickupschedule.id  in "
		// +
		// " (select pickupschedules_id from location_pickupschedule where location_id = "
		// + location.getId() + ")";
		// Query query = this.em.createNativeQuery(sql);
		// query.executeUpdate();
		Location loc = super.save(location);

		return loc;
	}

	@Override
	public List<Location> findAll() {
		String sql = "select * from location order by district desc";
		Query query = this.em.createNativeQuery(sql, Location.class);
		List<Location> locations = query.getResultList();
		return locations;
	}

}
